package com.hnkywl.sts.dao.xsgl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.dao.sys.YgDao;
import com.hnkywl.sts.entity.htgl.Cght;
import com.hnkywl.sts.entity.htgl.Xsht;
import com.hnkywl.sts.entity.organ.Ck;
import com.hnkywl.sts.entity.xsgl.Kh;
import com.hnkywl.sts.entity.xsgl.Xsmx;
import com.hnkywl.sts.service.sys.YgService;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.*;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.management.Query;
import java.math.BigDecimal;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: lenovo
 * Date: 13-5-8
 * Time: 下午11:45
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class XsmxDao extends SimpleHibernateTemplate<Xsmx, Long> {
    private YgDao ygDao;

    public Page<Xsmx> findXsmxList(Long ckId, String xsdh, String xslx, String djlx, Long sbgsId, Long sbbmId, Long khId, String ggName, String ggCode, String ggXh, Date fhrqStart, Date fhrqEnd, String zt, String jszt, Long gcId, int pageNo, int pageSize) {
        Page<Xsmx> page = new Page<Xsmx>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        Criteria criteria = getSession().createCriteria(Xsmx.class)
                .createAlias("jtwz", "wzgg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsd", "xsd", CriteriaSpecification.INNER_JOIN)
                .createAlias("xsd.fyzr", "fyzr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsd.kh", "khmc", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsd.gc", "gc", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsd.xsgs", "xsgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsd.sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsd.sbbm", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsd.sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsd.thck", "thck", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh.cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh.ck", "ck", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("xsd.xsdh").as("xsdh"))
                                .add(Property.forName("xsd.fhsj").as("fhsj"))
                                .add(Property.forName("xsd.sl").as("sl"))
                                .add(Property.forName("xsd.cksj").as("cksj"))
                                .add(Property.forName("xscbzh").as("xscbzh"))
                                .add(Property.forName("xslr").as("xslr"))
                                .add(Property.forName("xsd.sl").as("xsd_sl"))
                                .add(Property.forName("khmc.id").as("khmc_id"))
                                .add(Property.forName("khmc.jc").as("khmc_jc"))
                                .add(Property.forName("fyzr.id").as("fyzr_id"))
                                .add(Property.forName("fyzr.z").as("fyzr_z"))
                                .add(Property.forName("gc.id").as("gc_id"))
                                .add(Property.forName("gc.mc").as("gc_mc"))
                                .add(Property.forName("xsgs.id").as("xsgs_id"))
                                .add(Property.forName("xsgs.jc").as("xsgs_jc"))
                                .add(Property.forName("sbgs.id").as("sbgs_id"))
                                .add(Property.forName("sbgs.jc").as("sbgs_jc"))
                                .add(Property.forName("sbbm.id").as("sbbm_id"))
                                .add(Property.forName("sbbm.jc").as("sbbm_jc"))
                                .add(Property.forName("sbr.id").as("sbr_id"))
                                .add(Property.forName("sbr.xm").as("sbr_xm"))
                                .add(Property.forName("xsd.zt").as("zt"))
                                .add(Property.forName("xsd.yck").as("yck"))
                                .add(Property.forName("xsd.ysh").as("ysh"))
                                .add(Property.forName("xsxt_xsdh").as("xsxt_xsdh"))
                                .add(Property.forName("wzgg.id").as("wzgg_id"))
                                .add(Property.forName("wzgg.oldcode").as("wzgg_oldcode"))
                                .add(Property.forName("wzgg.code").as("wzgg_code"))
                                .add(Property.forName("cd.z").as("kh_cd"))
                                .add(Property.forName("wzgg.name").as("wzgg_name"))
                                .add(Property.forName("wzgg.ggxh").as("wzgg_ggxh"))
                                .add(Projections.sqlProjection("wzgg1_.cz||' '||wzgg1_.ggxh as wzgg_ggxh", new String[]{"wzgg_ggxh"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("wzgg.dw").as("wzgg_dw"))
                                .add(Projections.sqlProjection("sbsl as sbsl", new String[]{"sbsl"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("ck_sbsl as ck_sbsl", new String[]{"ck_sbsl"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("hsj as hsj", new String[]{"hsj"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("bhsj as bhsj", new String[]{"bhsj"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("this_.yjs as yjs", new String[]{"yjs"}, new Type[]{Hibernate.BOOLEAN}))
                                .add(Property.forName("dateCreated"))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(xsdh)) {
            criteria.add(Property.forName("xsd.xsdh").like(xsdh, MatchMode.START));
        }
        if (sbgsId != null) {
            criteria.add(Restrictions.eq("xsd.sbgs.id", sbgsId));
        }
        if (ckId != null) {
            criteria.add(Restrictions.eq("xsd.thck.id", ckId));
        }
        if (sbbmId != null) {
            criteria.add(Restrictions.eq("xsd.sbbm.id", sbbmId));
        }
        if (khId != null) {
            criteria.add(Restrictions.eq("xsd.kh.id", khId));
        }
        if (StringUtils.isNotEmpty(ggName)) {
            criteria.add(Property.forName("wzgg.name").like(ggName, MatchMode.START));
        }
        if (StringUtils.isNotEmpty(ggCode)) {
            criteria.add(Restrictions.or(Property.forName("wzgg.code").like(ggCode, MatchMode.START), Property.forName("wzgg.oldcode").like(ggCode, MatchMode.START)));
        }
        /*   if (StringUtils.isNotEmpty(gcmc)) {
            criteria.add(Property.forName("gc.mc").like(gcmc, MatchMode.START));
        }*/
        if (gcId != null) {
            criteria.add(Restrictions.eq("gc.id", gcId));
        }
        if (StringUtils.isNotEmpty(ggXh)) {
            criteria.add(Restrictions.or(
                    Property.forName("wzgg.ggxh").like(ggXh, MatchMode.START),
                    Property.forName("wzgg.cz").like(ggXh, MatchMode.START)
            ));
        }
        if (fhrqStart != null) {
            criteria.add(Restrictions.ge("xsd.cksj", sDaysAfterNowDate(fhrqStart)));
        } else {
            Date start = sDaysAfterNowDate(new Date());
            criteria.add(Restrictions.ge("xsd.cksj", start));
        }
        if (fhrqEnd != null) {
            criteria.add(Restrictions.le("xsd.cksj", jDaysAfterNowDate(fhrqEnd)));
        } else {
            Date end = jDaysAfterNowDate(new Date());
            criteria.add(Restrictions.le("xsd.cksj", end));
        }
        if (zt != null && !zt.isEmpty() && "yck".equals(zt)) {
            criteria.add(Restrictions.eq("xsd.yck", true));
        } else if (zt != null && !zt.isEmpty() && "wck".equals(zt)) {
            criteria.add(Restrictions.eq("xsd.yck", false));
        }

        if (jszt != null && !jszt.isEmpty() && "yjs".equals(jszt)) {
            criteria.add(Restrictions.eq("yjs", true));
        } else if (jszt != null && !jszt.isEmpty() && "wjs".equals(jszt)) {
            criteria.add(Restrictions.eq("yjs", false));
        }

        if (xslx != null && !xslx.isEmpty() && "kcls".equals(xslx)) {
            criteria.add(Restrictions.eq("xsd.xslx", xslx));
        } else if (xslx != null && !xslx.isEmpty() && "zddh".equals(xslx)) {
            criteria.add(Restrictions.eq("xsd.xslx", xslx));
        } else if (xslx != null && !xslx.isEmpty() && "htxs".equals(xslx)) {
            criteria.add(Restrictions.eq("xsd.xslx", xslx));
        } else if (xslx != null && !xslx.isEmpty() && "bgxs".equals(xslx)) {
            criteria.add(Restrictions.eq("xsd.xslx", xslx));
        } else if (xslx != null && !xslx.isEmpty() && "nbxs".equals(xslx)) {
            criteria.add(Restrictions.eq("xsd.xslx", xslx));
        }

        if (djlx != null && !djlx.isEmpty() && "wbxs".equals(djlx)) {
            criteria.add(Restrictions.or(Restrictions.eq("xsd.sfnb", false), Restrictions.isNull("xsd.sfnb")));
        } else if (djlx != null && !djlx.isEmpty() && "nbxs".equals(djlx)) {
            criteria.add(Restrictions.eq("xsd.sfnb", true));
        }


        criteria.add(Restrictions.isNotNull("xsd"));
        criteria.add(Restrictions.eq("xsd.sftzd", false));
        //RegionDao.createDataRegion(criteria);
        createXsmxDataRegion(criteria);
        criteria.addOrder(Property.forName("cksj").desc());
        criteria.addOrder(Property.forName("wzgg.oldcode").asc());
        Page list = this.findByCriteria(page, criteria);
        return list;
    }

    private void createXsmxDataRegion(Criteria criteria) {
        criteria.createAlias("createUser", "createUser", CriteriaSpecification.LEFT_JOIN);
        List<Ck> list = this.ygDao.get(YgService.getCurrentYg().getId()).getCkList();
        if (list != null && list.size() > 0) {
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    break;
                case 1:
                    criteria.add(Restrictions.in("kh.ck", list));
                    break;
                case 2:
                    criteria.add(Restrictions.in("kh.ck", list));
                    break;
                case 3:
                    criteria.add(Restrictions.in("kh.ck", list));
                    break;
            }
        } else {
            SimpleExpression se = null;
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    return;
                case 1:
                    se = Restrictions.eq("createOrgan.id", YgService.getCurrentYgZgGsId());
                    break;
                case 2:
                    se = Restrictions.eq("createDepartment.id", YgService.getCurrentYgGsId());
                    break;
                case 3:
                    se = Restrictions.eq("createUser.id", YgService.getCurrentYg().getId());
                    break;
            }

            criteria.add(
                    Restrictions.or(se,
                            Restrictions.eq("xsd.sbgs.id", YgService.getCurrentYgZgGsId())
                    )
            );
        }
    }

    private String createSQLDataRegion(String joinStr, String alias) {
        List<Ck> list = this.ygDao.get(YgService.getCurrentYg().getId()).getCkList();
        if (list != null && list.size() > 0) {
            String ids = "";
            for (Ck ck : list) {
                ids += ck.getId() + ",";
            }
            ids = ids.substring(0, ids.length() - 1);


//            alias = StringUtils.isEmpty(alias) ? "" : alias + ".";
            joinStr = StringUtils.isEmpty(joinStr) ? "" : joinStr + " and ";
            String regionSql = "";
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    regionSql += " {0} 1=1";
                    break;
                case 1:
                    regionSql += " {0} ({1}ck_id in (" + ids + "))";
                    break;
                case 2:
                    regionSql += " {0} ({1}ck_id in (" + ids + "))";
                    break;
                case 3:
                    regionSql += " {0} ({1}ck_id in (" + ids + "))";
                    break;
            }
            return regionSql.replace("{0}", joinStr).replace("{1}", "kh.");
        } else {
            alias = StringUtils.isEmpty(alias) ? "" : alias + ".";
            joinStr = StringUtils.isEmpty(joinStr) ? "" : joinStr + " and ";
            String regionSql = "";
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    regionSql += " {0} 1=1";
                    break;
                case 1:
                    regionSql += " {0} ( {1}CREATEORGAN_ID =" + YgService.getCurrentYgZgGsId();
                    break;
                case 2:
                    regionSql += "  {0} ( {1}CREATEDEPARTMENT_ID =" + YgService.getCurrentYgGsId();
                    break;
                case 3:
                    regionSql += "  {0} ( {1}CREATEDEPARTMENT_ID =" + YgService.getCurrentYgGsId();
                    regionSql += "  {0}   {1}createuser_id =" + YgService.getCurrentYg().getId();
                    break;
            }

            if (YgService.getCurrentYgCxfw() != 0) {
                regionSql += " or {1}id in (select xsd.id from t_biz_xsd xsd where xsd.deleted!=1 and xsd.sbgs_id =" + YgService.getCurrentYgZgGsId() + "))";
            }

            return regionSql.replace("{0}", joinStr).replace("{1}", alias);
        }

    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public Date jDaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 23);
        rightNow.set(java.util.Calendar.MINUTE, 59);
        rightNow.set(java.util.Calendar.SECOND, 59);
        return rightNow.getTime();
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public Date sDaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 0);
        rightNow.set(java.util.Calendar.MINUTE, 0);
        rightNow.set(java.util.Calendar.SECOND, 0);
        return rightNow.getTime();
    }

    public List<Map> findSumXsmx(Long ckId, String xsdh, String xslx, String djlx, Long sbgsId, Long sbbmId, Long khId, String ggName, String ggCode, String ggXh, Date fhrqStart, Date fhrqEnd, String zt, String jszt, Long gcId) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sql = "select sum((ROUND(ROUND(t.HSJ * t.CK_SBSL, 2) / (1 + xsd3_.SL / 100), 2) - ROUND(t.xscb*t.ck_sbsl,2))) as \"xslr\",sum(ROUND(t.xscb*t.ck_sbsl,2)) as \"xscbzh\",sum(round(t.sbsl,4)) as \"sbsl\", sum(round(t.ck_sbsl,4)) as \"ck_sbsl\", sum(round(t.jsbhse,2)) as \"jsje1\",sum(round(round(kh.sl,4)*kh.wsdj,2)) as \"kcje1\",sum(round(round(t.ck_sbsl,4)*t.hsj,2)) as \"hse\",sum(round(round(round(t.ck_sbsl,4)*t.hsj,2)/(1+xsd3_.sl/100),2)) as \"bhse\" from t_biz_xsmx t " +
                " left outer join T_SYS_GG wzgg1_ on t.JTWZ_ID = wzgg1_.id" +
                " left join t_biz_kh kh on t.kh_id = kh.id" +
                " inner join T_BIZ_XSD xsd3_ on t.XSD_ID = xsd3_.id  left outer join T_SYS_CK thck11_ on xsd3_.CK_ID = thck11_.id" +
                " left outer join T_SYS_GSJG kh5_ on xsd3_.KH_ID = kh5_.id" +
                " left outer join T_SYS_GSJG sbgs7_ on xsd3_.SBGS_ID = sbgs7_.id" +
                " left outer join T_SYS_GSJG sbbm8_ on xsd3_.SBBM_ID = sbbm8_.id" +
                " left outer join T_SYS_GC  gc on xsd3_.gc_id=gc.id";
        if (gcId != null) {
            condition.add(" gc.id=:gc_id ");
            param.put("gc_id", gcId);
        }
        if (ckId != null) {
            condition.add(" thck11_.id=:thck_id ");
            param.put("thck_id", ckId);
        }
        if (StringUtils.isNotEmpty(xsdh)) {
            condition.add(" xsd3_.xsdh like :xsdh ");
            param.put("xsdh", xsdh + "%");
        }
        if (fhrqStart != null) {
            condition.add(" xsd3_.cksj>= :fhrqStart ");
            param.put("fhrqStart", sDaysAfterNowDate(fhrqStart));
        } else {
            condition.add(" xsd3_.cksj>= :fhrqStart ");
            param.put("fhrqStart", sDaysAfterNowDate(new Date()));
        }
        if (fhrqEnd != null) {
            condition.add(" xsd3_.cksj<= :fhrqEnd ");
            param.put("fhrqEnd", jDaysAfterNowDate(fhrqEnd));
        } else {
            condition.add(" xsd3_.cksj<= :fhrqEnd ");
            param.put("fhrqEnd", jDaysAfterNowDate(new Date()));
        }

        if (jszt != null && !jszt.isEmpty() && "yjs".equals(jszt)) {
            condition.add(" t.yjs=:jszt ");
            param.put("jszt", true);
        } else if (jszt != null && !jszt.isEmpty() && "wjs".equals(jszt)) {
            condition.add(" t.yjs=:jszt ");
            param.put("jszt", false);
        }

        if (zt != null && !zt.isEmpty() && "yck".equals(zt)) {
            condition.add(" xsd3_.yck=:zt ");
            param.put("zt", true);
        } else if (zt != null && !zt.isEmpty() && "wck".equals(zt)) {
            condition.add(" xsd3_.yck=:zt ");
            param.put("zt", false);
        }

        if (xslx != null && !xslx.isEmpty() && "kcls".equals(xslx)) {
            condition.add(" xsd3_.xslx=:xslx ");
            param.put("xslx", xslx);
        } else if (xslx != null && !xslx.isEmpty() && "zddh".equals(xslx)) {
            condition.add(" xsd3_.xslx=:xslx ");
            param.put("xslx", xslx);
        } else if (xslx != null && !xslx.isEmpty() && "htxs".equals(xslx)) {
            condition.add(" xsd3_.xslx=:xslx ");
            param.put("xslx", xslx);
        } else if (xslx != null && !xslx.isEmpty() && "bgxs".equals(xslx)) {
            condition.add(" xsd3_.xslx=:xslx ");
            param.put("xslx", xslx);
        } else if (xslx != null && !xslx.isEmpty() && "nbxs".equals(xslx)) {
            condition.add(" xsd3_.xslx=:xslx ");
            param.put("xslx", xslx);
        }

        if (djlx != null && !djlx.isEmpty() && "wbxs".equals(djlx)) {
            condition.add(" ( xsd3_.sfnb is null or xsd3_.sfnb =:sfnb ) ");
            param.put("sfnb", false);
        } else if (djlx != null && !djlx.isEmpty() && "nbxs".equals(djlx)) {
            condition.add(" xsd3_.sfnb=:sfnb ");
            param.put("sfnb", true);
        }

        if (sbgsId != null) {
            condition.add(" sbgs7_.id=:sbgs_id ");
            param.put("sbgs_id", sbgsId);
        }

        if (sbbmId != null) {
            condition.add(" sbbm8_.id=:sbbm_id ");
            param.put("sbbm_id", sbbmId);
        }

        if (khId != null) {
            condition.add(" kh5_.id=:kh_id ");
            param.put("kh_id", khId);
        }

        if (StringUtils.isNotEmpty(ggName)) {
            condition.add(" wzgg1_.name like:wzgg_name ");
            param.put("wzgg_name", "%" + ggName + "%");
        }

        if (StringUtils.isNotEmpty(ggCode)) {
            condition.add("( wzgg1_.code like :wzgg_code or wzgg1_.oldcode like :oldcode) ");
            param.put("wzgg_code", ggCode + "%");
            param.put("oldcode", ggCode + "%");
        }

        if (StringUtils.isNotEmpty(ggXh)) {
            condition.add("( wzgg1_.ggxh like:wzgg_ggxh or  wzgg1_.cz like:wzgg_cz )");
            param.put("wzgg_ggxh", "%" + ggXh + "%");
            param.put("wzgg_cz", "%" + ggXh + "%");
        }

        condition.add(" xsd3_.sftzd=0 ");


        //String dataRegion = RegionDao.createSQLDataRegion(null, "t");
        String dataRegion = createSQLDataRegion(null, "xsd3_");
        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = StringUtils.join(condition, " and ") + " and ";

        sql += " where t.deleted!=1 and t.xsd_id is not null and " + whereSql + dataRegion;

        SQLQuery sqlQuery = getSession().createSQLQuery(sql);
        List<Map> list = sqlQuery
                .setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        return list;
    }


    public Map<String, List> exportxsmx(String xsdh, String xslx, String djlx, Long sbgsId, Long sbbmId, Long khId, String ggName, String ggCode, String ggXh, Date fhrqStart, Date fhrqEnd, String zt, String jszt, Long gcId, Long ckId) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sql = "select dense_rank() over(order by c.xsdh) \"xh\", c.xsdh as \"xsdh\",c.fhsj as \"fhsj\",c.date_created as \"dateCreated\",c.cksj as \"cksj\",gg.code as \"wzggcode\",gg.oldcode as \"gg.oldcode\",gg.name as \"ggName\"," +
                "  gg.cz||' '||gg.ggxh as \"wzggggxh\",gg.cz as \"gg.cz\",gg.dw as \"dw\",gg.th as \"th\",round(t.sbsl,4) as \"sbsl2\"," +
                "  round(round(t.ck_sbsl,4)*t.hsj,2) as \"csje\",round(t.ck_sbsl,4) as \"sbsl1\",round(t.bhsj,4) as \"bhsj\",round(decode(c.yjs,0,round(round(t.ck_sbsl,4)*t.hsj,2)/(1+c.sl/100),1,round(t.jsbhse,2)),2) as \"jsje\"," +
                "  round(decode(c.yjs,0,(round(round(t.ck_sbsl,4)*t.hsj,2)/(1+c.sl/100))*0.17,1,t.jsse),2) as \"se\",round(t.ck_sbsl,4) as \"cksbsl\"," +
                "  round(kh.wsdj,4) as \"kcdj\",round(round(t.ck_sbsl,4) * kh.wsdj,2) as \"kcje\",xsjs.hm as \"fphm\",gc.id as \"gcId\"," +
                "  gc.mc as \"gcmc\",g.id as \"khId\",g.jc as \"khJc\",nvl(g3.jc,g4.jc) as \"gysJc\",g1.id as \"sbgsId\",g1.jc as \"sbgsJc\"," +
                "  g2.id as \"sbbmId\",g2.jc as \"sbbmJc\",sjzdz1.z as \"cd\",sjzdz2.z as \"fyzr\" ";
        String sumSql = "select sum(round(t.sbsl,4)) as \"sbsl\",sum(round(round(t.ck_sbsl,4)*t.hsj,2)) as \"csje\",sum(round(t.ck_sbsl,4)) as \"ck_sbsl\", sum(round(decode(c.yjs,0,(round(round(t.ck_sbsl,4)*t.hsj,2)/(1+c.sl/100))*0.17,1,round(t.jsse,2)),2)) as \"hse\", sum(round(decode(c.yjs,0,round(round(t.ck_sbsl,4)*t.hsj,2)/(1+c.sl/100),1,round(t.jsbhse,2)),2)) as \"jsje\",sum(round(round(t.ck_sbsl,4) *kh.wsdj,2)) as \"kcje\"";
        String from = " from t_biz_xsmx t " +
                " inner join t_biz_xsd c on t.xsd_id = c.id " +
                "  left join t_biz_jsd xsjs on t.xsjsd_id = xsjs.id " +
                "  left join t_sys_gc gc on c.gc_id = gc.id " +
                "  left join t_sys_gsjg g on c.kh_id = g.id " +
                "  left join t_biz_kh kh on t.kh_id = kh.id " +
                "  left join t_biz_cgd cgd on kh.cgd_id = cgd.id " +
                "  left join t_sys_gsjg g3 on cgd.gys_id = g3.id " +

                "  left join t_biz_cgd cgd1 on c.id = cgd1.xsd_id " +
                "  left join t_sys_gsjg g4 on cgd1.gys_id = g4.id " +

                " left join t_sys_sjzdz sjzdz1 on kh.cd_sjzdz_id=sjzdz1.id" +
                " left join t_sys_sjzdz sjzdz2 on c.fyzr_sjzdz_id=sjzdz2.id" +

                "  left join t_sys_gg gg on gg.id = t.jtwz_id " +
                "  left join t_sys_gsjg g1 on c.sbgs_id = g1.id " +
                "  left join t_sys_gsjg g2 on c.sbbm_id = g2.id";
//        StringBuilder where = new StringBuilder();
        if (StringUtils.isNotEmpty(xsdh)) {
            condition.add(" c.xsdh like :xsdh ");
            param.put("xsdh", xsdh + "%");
        }
        if (khId != null) {
            condition.add(" g.id = :khId ");
            param.put("khId", khId);
        }
        if (sbgsId != null) {
            condition.add(" c.sbgs_id = :sbgsId ");
            param.put("sbgsId", sbgsId);
        }
        if (sbbmId != null) {
            condition.add(" c.sbbm_id = :sbbmId ");
            param.put("sbbmId", sbbmId);
        }
        if (ckId != null) {
            condition.add(" c.ck_id = :ckId ");
            param.put("ckId", ckId);
        }
        if (StringUtils.isNotEmpty(ggCode)) {
            condition.add(" (gg.code like :code or gg.oldcode like :oldcode)");
            param.put("code", ggCode + "%");
            param.put("oldcode", ggCode + "%");
        }
        if (StringUtils.isNotEmpty(ggName)) {
            condition.add(" gg.name like :ggName ");
            param.put("ggName", "%" + ggName + "%");
        }
        if (StringUtils.isNotEmpty(ggXh)) {
            condition.add(" (gg.code like :ggxh or gg.code like :cz)");
            param.put("ggxh", "%" + ggXh + "%");
            param.put("cz", "%" + ggXh + "%");
        }
        /* if (StringUtils.isNotEmpty(gcmc)) {
            condition.add(" gc.mc like :gcmc ");
            param.put("gcmc", gcmc + "%");
        }*/
        if (gcId != null) {
            condition.add(" gc.id = :gcId ");
            param.put("gcId", gcId);
        }
        if (fhrqStart != null) {
            condition.add(" c.cksj>= :fhrqStart ");
            param.put("fhrqStart", sDaysAfterNowDate(fhrqStart));
        } else {
            condition.add(" c.cksj>= :fhrqStart ");
            param.put("fhrqStart", sDaysAfterNowDate(new Date()));
        }
        if (fhrqEnd != null) {
            condition.add(" c.cksj<= :fhrqEnd ");
            param.put("fhrqEnd", jDaysAfterNowDate(fhrqEnd));
        } else {
            condition.add(" c.cksj<= :fhrqEnd ");
            param.put("fhrqEnd", jDaysAfterNowDate(new Date()));
        }

        if (jszt != null && !jszt.isEmpty() && "yjs".equals(jszt)) {
            condition.add(" t.yjs=:jszt ");
            param.put("jszt", true);
        } else if (jszt != null && !jszt.isEmpty() && "wjs".equals(jszt)) {
            condition.add(" t.yjs=:jszt ");
            param.put("jszt", false);
        }

        if (zt != null && !zt.isEmpty() && "yck".equals(zt)) {
            condition.add(" c.yck=:zt ");
            param.put("zt", true);
        } else if (zt != null && !zt.isEmpty() && "wck".equals(zt)) {
            condition.add(" c.yck=:zt ");
            param.put("zt", false);
        }

        if (djlx != null && !djlx.isEmpty() && "wbxs".equals(djlx)) {
            condition.add("( c.sfnb is null or c.sfnb =:sfnb ) ");
            param.put("sfnb", false);
        } else if (djlx != null && !djlx.isEmpty() && "nbxs".equals(djlx)) {
            condition.add(" c.sfnb=:sfnb ");
            param.put("sfnb", true);
        }

        if (xslx != null && !xslx.isEmpty() && "kcls".equals(xslx)) {
            condition.add(" c.xslx=:xslx ");
            param.put("xslx", xslx);
        } else if (xslx != null && !xslx.isEmpty() && "zddh".equals(xslx)) {
            condition.add(" c.xslx=:xslx ");
            param.put("xslx", xslx);
        } else if (xslx != null && !xslx.isEmpty() && "htxs".equals(xslx)) {
            condition.add(" c.xslx=:xslx ");
            param.put("xslx", xslx);
        } else if (xslx != null && !xslx.isEmpty() && "bgxs".equals(xslx)) {
            condition.add(" c.xslx=:xslx ");
            param.put("xslx", xslx);
        } else if (xslx != null && !xslx.isEmpty() && "nbxs".equals(xslx)) {
            condition.add(" c.xslx=:xslx ");
            param.put("xslx", xslx);
        }

        condition.add("c.sftzd=0");

        String dataRegion = createSQLDataRegion(null, "c");

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = StringUtils.join(condition, " and ") + " and ";

        sql += from + " where t.deleted!=1 and t.xsd_id is not null and " + whereSql + dataRegion + " order by c.xsdh";
        sumSql += from + " where t.deleted!=1 and t.xsd_id is not null and " + whereSql + dataRegion;
        SQLQuery query = getSession().createSQLQuery(sql);
        SQLQuery sumQuery = getSession().createSQLQuery(sumSql);
        List<Map> list = query
                .setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();

        List<Map> sum = sumQuery
                .setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        Map<String, List> map = new HashMap<String, List>();
        map.put("result", list);
        map.put("sum", sum);
        return map;
    }

    //销售明细报表
    public List<Map> exportxsmxbb(Long ckId, String xsdh, String xslx, String djlx, Long sbgsId, Long sbbmId, Long khId, String ggName, String ggCode, String ggXh, Date fhrqStart, Date fhrqEnd, String zt, String jszt, Long gcId) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sql = "";
        String colSql = "select t.wzid, t.wzmc, count(*) djsl, round(sum(t.ckje),2) ckje, round(sum(t.xsje),2) xsje";
        String sqlContent = " select (select rj.yjwzbm_id " +
                " from T_SYS_RJWZBM rj, T_SYS_PM pm, T_SYS_GG gg " +
                " where rj.id = pm.rjwzbm_id " +
                " and pm.id = gg.pm_id and gg.id = mx.jtwz_id) wzid, " +
                " (select '('||yj.code||')'||yj.name from T_SYS_YJWZBM yj,T_SYS_RJWZBM rj, T_SYS_PM  pm, T_SYS_GG gg " +
                "  where yj.id = rj.yjwzbm_id and rj.id = pm.rjwzbm_id and pm.id = gg.pm_id and gg.id = mx.jtwz_id) wzmc,(mx.ck_sbsl * mx.xscb) ckje, " +
                " (mx.ck_sbsl * mx.bhsj) xsje from T_BIZ_XSMX mx left join t_biz_xsd x on x.id = mx.xsd_id left join t_sys_ck thck on x.ck_id = thck.id " +
                " left join t_biz_kh kh on kh.id = x.kh_id left join t_sys_gsjg g " +
                " on g.id = x.xsgs_id left join t_sys_gc gc on gc.id = x.gc_id left join t_sys_gg wz " +
                " on wz.id = mx.jtwz_id where x.deleted !=1 and";
        String group = "  group by t.wzid, t.wzmc order by t.wzid ";

        if (StringUtils.isNotEmpty(xsdh)) {
            condition.add(" x.xsdh like :xsdh ");
            param.put("xsdh", "%" + xsdh + "%");
        }
        if (khId != null) {
            condition.add(" x.kh_id = :khId ");
            param.put("khId", khId);
        }
        if (ckId != null) {
            condition.add(" thck.id = :ckId ");
            param.put("ckId", ckId);
        }
        if (sbgsId != null) {
            condition.add(" x.sbgs_id = :sbgsId ");
            param.put("sbgsId", sbgsId);
        }
        if (sbbmId != null) {
            condition.add(" x.sbbm_id = :sbbmId ");
            param.put("sbbmId", sbbmId);
        }
        if (StringUtils.isNotEmpty(ggCode)) {
            condition.add(" (wz.code like :code or wz.oldcode like :oldcode)");
            param.put("code", ggCode + "%");
            param.put("oldcode", ggCode + "%");
        }
        if (StringUtils.isNotEmpty(ggName)) {
            condition.add(" wz.name like :ggName ");
            param.put("ggName", "%" + ggName + "%");
        }
        if (StringUtils.isNotEmpty(ggXh)) {
            condition.add(" (wz.code like :ggxh or wz.code like :cz)");
            param.put("ggxh", "%" + ggXh + "%");
            param.put("cz", "%" + ggXh + "%");
        }
        /*if (StringUtils.isNotEmpty(gcmc)) {
            condition.add(" gc.mc like :gcmc ");
            param.put("gcmc", gcmc + "%");
        }*/
        if (gcId != null) {
            condition.add(" gc.id = :gcId ");
            param.put("gcId", gcId);
        }
        if (fhrqStart != null) {
            condition.add(" x.cksj>= :fhrqStart ");
            param.put("fhrqStart", sDaysAfterNowDate(fhrqStart));
        } else {
            condition.add(" x.cksj>= :fhrqStart ");
            param.put("fhrqStart", sDaysAfterNowDate(new Date()));
        }
        if (fhrqEnd != null) {
            condition.add(" x.cksj<= :fhrqEnd ");
            param.put("fhrqEnd", jDaysAfterNowDate(fhrqEnd));
        } else {
            condition.add(" x.cksj<= :fhrqEnd ");
            param.put("fhrqEnd", jDaysAfterNowDate(new Date()));
        }
        if (jszt != null && !jszt.isEmpty() && "yjs".equals(jszt)) {
            condition.add(" mx.yjs=:jszt ");
            param.put("jszt", true);
        } else if (jszt != null && !jszt.isEmpty() && "wjs".equals(jszt)) {
            condition.add(" mx.yjs=:jszt ");
            param.put("jszt", false);
        }

        if (zt != null && !zt.isEmpty() && "yck".equals(zt)) {
            condition.add(" x.yck=:zt ");
            param.put("zt", true);
        } else if (zt != null && !zt.isEmpty() && "wck".equals(zt)) {
            condition.add(" x.yck=:zt ");
            param.put("zt", false);
        }

        if (djlx != null && !djlx.isEmpty() && "wbxs".equals(djlx)) {
            condition.add("( x.sfnb is null or x.sfnb =:sfnb ) ");
            param.put("sfnb", false);
        } else if (djlx != null && !djlx.isEmpty() && "nbxs".equals(djlx)) {
            condition.add(" x.sfnb=:sfnb ");
            param.put("sfnb", true);
        }

        if (xslx != null && !xslx.isEmpty() && "kcls".equals(xslx)) {
            condition.add(" x.xslx=:xslx ");
            param.put("xslx", xslx);
        } else if (xslx != null && !xslx.isEmpty() && "zddh".equals(xslx)) {
            condition.add(" x.xslx=:xslx ");
            param.put("xslx", xslx);
        } else if (xslx != null && !xslx.isEmpty() && "htxs".equals(xslx)) {
            condition.add(" x.xslx=:xslx ");
            param.put("xslx", xslx);
        } else if (xslx != null && !xslx.isEmpty() && "bgxs".equals(xslx)) {
            condition.add(" x.xslx=:xslx ");
            param.put("xslx", xslx);
        } else if (xslx != null && !xslx.isEmpty() && "nbxs".equals(xslx)) {
            condition.add(" x.xslx=:xslx ");
            param.put("xslx", xslx);
        }

        condition.add("x.sftzd=0");

        String dataRegion = createSQLDataRegion(null, "x");

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = StringUtils.join(condition, " and ") + " and ";

        sql = colSql + " from " + "(" + sqlContent + whereSql + dataRegion + ") t " + group;

        SQLQuery query = getSession().createSQLQuery(sql);

        List<Map> list = query
                .setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        return list;
    }

    //赎货明细报表
    public Map<String, Object> shmxList(Long gsId, Long gysId, Long khId, String cght, String xsd, Date start, Date end, int pageNo, int pageSize) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String fields = "select wly.jc \"wly\",gys.jc \"gys\",cght.htbm \"cght\",kh.qc \"kh\",xsd.xsdh \"xsdh\",gg.name \"gg.name\",gg.code \"gg.code\", mx.date_created \"shrq\"," +
                "gg.ggxh || '  ' || gg.cz \"ggxh\" ,mx.hsj \"hsj\",mx.ck_sbsl \"sl\",mx.hsj * mx.ck_sbsl \"she\",mx.yhjg \"yhjg\",mx.yhjg*mx.ck_sbsl \"yhe\"";
        String count = "select count(1)";
        String sum = "select sum(mx.hsj * mx.ck_sbsl) \"she\",sum(mx.yhjg*mx.ck_sbsl) \"yhe\"";
        String from = "  from t_biz_xsmx mx " +
                "  left join t_biz_xsd xsd on mx.xsd_id = xsd.id and xsd.deleted = 0 " +
                "  left join t_biz_kh k on k.id = mx.kh_id " +
                "  left join t_biz_cgdmx cgdmx on cgdmx.id = k.cgdmx_id " +
                "  left join t_biz_cgd cgd on cgdmx.cgd_id = cgd.id" +
                "  left join t_biz_ht cght on cgd.cght_id = cght.id and cght.deleted = 0 and cght.type = 'cght' " +
                "  left join t_sys_gg gg on mx.jtwz_id = gg.id " +
                "  left join t_sys_gsjg gys on cght.hzf_id = gys.id and gys.deleted = 0 " +
                "  left join t_sys_gsjg kh on xsd.kh_id = kh.id and kh.deleted = 0 " +
                "  left join t_sys_gsjg sbgs on xsd.sbgs_id = sbgs.id and kh.deleted = 0 " +
                "  left join t_sys_gsjg wly on cght.sbgs_id = wly.id and wly.deleted = 0 ";
        String where = "";
        condition.add(" xsd.yck = 1 ");
        condition.add(" mx.kh_id is not null ");
        condition.add(" sbgs.type = 'nbgs' ");
        if (gsId != null) {
            condition.add(" wly.id = :wly ");
            param.put("wly", gsId);
        }
        if (gysId != null) {
            condition.add(" gys.id = :gys ");
            param.put("gys", gysId);
        }
        if (khId != null) {
            condition.add(" kh.id = :kh ");
            param.put("kh", khId);
        }
        if (StringUtils.isNotEmpty(cght)) {
            condition.add(" cght.htbm = :cght ");
            param.put("cght", cght);
        }
        if (StringUtils.isNotEmpty(xsd)) {
            condition.add(" xsd.xsdh = :xsd ");
            param.put("xsd", xsd);
        }
        if (start != null) {
            condition.add(" mx.date_created >= :dateStart ");
            param.put("dateStart", start);
        }
        if (end != null) {
            condition.add(" mx.date_created <= :dateEnd ");
            param.put("dateEnd", end);
        }
        if (!condition.isEmpty())
            where = " where " + StringUtils.join(condition, " and ");
        SQLQuery cntQuery = getSession().createSQLQuery(count + from + where);
        SQLQuery rowQuery = getSession().createSQLQuery(fields + from + where);
        SQLQuery sumQuery = getSession().createSQLQuery(sum + from + where);
        int c = ((BigDecimal) cntQuery.setProperties(param).uniqueResult()).intValue();
        List<Map<String, Object>> r = rowQuery.setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .setFirstResult((pageNo - 1) * pageSize)
                .setMaxResults(pageSize)
                .list();
        List<Map<String, Object>> s = sumQuery.setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        Map<String, Object> result = new HashMap<String, Object>();
        result.put("count", c);
        result.put("rows", r);
        result.put("footer", s);
        return result;
    }


    public Page<Map> findSskcList(String xszxh, String carNo, String bindNo, String type, Long xshtId, int pageNo, int pageSize) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        Page<Map> page = new Page<Map>();

        //==============记录数量===============
        String sqlCountBase = "select count(1) from ";

        //==============查询结果===============
        String sqlBase = "select kh.khbm \"kh.khbm\",\n" +
                "       kh.id \"kh.id\",\n" +
                "       gg.id \"wzgg.id\",\n" +
                "       gg.name \"wzgg.name\",\n" +
                "       gg.code \"wzgg.code\",\n" +
                "       gg.cz \"wzgg.cz\",\n" +
                "       gg.ggxh \"wzgg.ggxh\",\n" +
                "       gg.dw \"wzgg.dw\",\n" +
                "       jzfs.id \"jzfs.id\",\n" +
                "       jzfs.z \"jzfs.z\",\n" +
                "       nvl(kh.wsdj, 0) \"xscb\",\n" +
                "       kh.js \"js\",\n" +
                "       kh.kkdsl \"sbsl\",\n" +
                "       kh.rksl \"rksl\",\n" +
                "       to_char(kh.rkrq,'yyyy-MM-dd') \"rkrq\",\n" +
                "       to_char(kh.ckrq,'yyyy-MM-dd') \"ckrq\",\n" +
                "       kh.carNo \"carNo\",\n" +
                "       kh.bindNo \"bindNo\",\n" +
                "       kh.xsxt_xsdh \"xszxh\",\n" +
                "       kh.kkdsl \"kkdsl\",\n" +
                "       nvl(kh.wsdj, 0) \"bhsj\",\n" +
                "       nvl(kh.wsdj, 0) * (1 + nvl(cght.sl, 17) * 0.01) \"hsj\",\n" +
                "       nvl(kh.wsdj, 0) * nvl(kh.kkdsl, 0) \"bhse\",\n" +
                "       (nvl(kh.wsdj, 0) * (1 + nvl(cght.sl, 17) * 0.01)) * nvl(kh.kkdsl, 0) \"hse\"\n" +
                "  from t_biz_ht    xsht,\n" +
                "       t_biz_ht    cght,\n" +
                "       t_biz_cgd   cgd,\n" +
                "       t_biz_cgdmx cgdmx,\n" +
                "       t_sys_sjzdz jzfs,\n" +
                "       t_biz_kh    kh,\n" +
                "       t_sys_gg    gg\n" +
                " where xsht.zddhcght_id = cght.id\n" +
                "   and cght.id = cgd.cght_id\n" +
                "   and cgd.id = cgdmx.cgd_id\n" +
                "   and cgdmx.szjdz_jzfs_id = jzfs.id\n" +
                "   and cgdmx.id = kh.cgdmx_id\n" +
                "   and kh.jtwz_id = gg.id";

        String whereSql = "";
        String orderBy = "";
        if (xshtId != null) {
            condition.add(" xsht.id = :xshtId ");
            param.put("xshtId", xshtId);
        }

        if (xszxh != null && !xszxh.isEmpty()) {
            condition.add(" kh.xsxt_xsdh like :xszxh ");
            param.put("xszxh", "%" + xszxh.trim().toUpperCase() + "%");
        }

        if (carNo != null && !carNo.isEmpty()) {
            condition.add(" kh.carNo like :carNo ");
            param.put("carNo", "%" + carNo.trim().toUpperCase() + "%");
        }

        if (bindNo != null && !bindNo.isEmpty()) {
            condition.add(" kh.bindNo like :bindNo ");
            param.put("bindNo", "%" + bindNo.trim().toUpperCase() + "%");
        }

        if (StringUtils.equals("1", type)) {
            condition.add(" kh.kkdsl = :kkdsl ");
            param.put("kkdsl", 0);
            orderBy += " order by kh.ckrq desc ";
        } else {
            orderBy += " order by kh.kkdsl desc,kh.ckrq desc ";
        }

        if (!condition.isEmpty())
            whereSql += " and " + StringUtils.join(condition, " and ");
        String countSql = sqlCountBase + " ( " + sqlBase + whereSql + " ) ";
        String resultSql = sqlBase + whereSql + orderBy;
        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    public List<Map> findCght(String xsdh) {
        String sql = "select h.id as id from t_biz_kh k left join t_biz_cgd c on k.cgd_id =c.id left join t_biz_ht h " +
                "on c.cght_id = h.id  where k.xsxt_xsdh = ? ";
        return (List<Map>) this.createSQLQuery(sql, xsdh).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();

    }

    public Xsmx findXsmxByBindNo(String bindNo) {
        String hql = "from Xsmx where bindNo = ? and xsd.zt != 'ygk' ";
        List<Xsmx> list = this.find(hql, bindNo);
        if (null != list && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }
    public List<Xsmx> getXsmx(Long xsdId) {
        String hql = " from  Xsmx where xsd.id=?";
        return this.find(hql, xsdId);
    }

    @Autowired
    public void setYgDao(YgDao ygDao) {
        this.ygDao = ygDao;
    }


}
